//导入数据库操作模块
const express = require('express');
const db = require('../database/index');


// 用户发布文章的处理函数
exports.addPost = (req, res) => {

    console.log('请求：用户发布文章')

    if (!req.body.title || !req.body.content) {
        return res.send({ status: 1, message: '标题或内容不能为空!' });
    }

    // sql语句
    const sql = 'insert into post set ?'

    db.query(sql, {
        PostTitle: req.body.title,
        PostContent: req.body.content,
        Post_UserID: req.user.UserID,
        Post_Views: 0,
        Post_Date: new Date(),
        Post_Update_Date: new Date()
    }, (err, results) => {

        // sql执行失败
        if (err) return res.send({ status: 1, message: err.message });

        // 判断影响行数是否为1
        if (results.affectedRows !== 1){
            return res.send({ status: 1,message: '发布博客失败,请稍后再试' });
        } 

        // 发布文章成功
        res.send({ status: 0, message: '发布博客成功!' });
    })

};

// 获取所有博客信息以及博客的分类和标签
exports.getAllPost = (req, res) => {

    console.log('请求：获取所有博客信息以及博客的分类和标签')

    const sql = `
        SELECT 
            p.PostID,
            p.PostTitle,
            p.PostContent,
            DATE_FORMAT(p.Post_Date, '%Y-%m-%d %H:%i:%s') as Post_Date,
            DATE_FORMAT(p.Post_Update_Date, '%Y-%m-%d %H:%i:%s') as Post_Update_Date,
            p.Post_Views,
            u.UserName,
            u.UserPhone,
            GROUP_CONCAT(DISTINCT t.TagName ORDER BY t.TagName SEPARATOR ',') AS TagNames,
            GROUP_CONCAT(DISTINCT c.ColumnName ORDER BY c.ColumnName SEPARATOR ',') AS ColumnNames
        FROM post p
        LEFT JOIN user u ON p.Post_UserID = u.UserID
        LEFT JOIN post_tag_relation ptr ON p.PostID = ptr.PT_PostID
        LEFT JOIN tag t ON ptr.PT_TagID = t.TagID
        LEFT JOIN post_column_relation pcr ON p.PostID = pcr.PC_PostID
        LEFT JOIN column_info c ON pcr.PC_ColumnID = c.ColumnID
        GROUP BY p.PostID 
        ORDER BY p.Post_Date DESC`

    // 执行sql语句
    db.query(sql, (err, results) => {

        // sql执行失败
        if (err) return res.send({ status: 1, message: err.message });

        // 查询结果为空
        if (results.length === 0) return res.send({ status: 1, message: '查询失败' });

        // 查询成功
        res.send({ status: 0, message: '获取博客列表成功', data: results });

    });



};

// 根据用户的ID获取用户的所有博客信息 （我的博客）
exports.getPostByUserId = (req, res) => {

    console.log('请求：根据用户的ID获取用户的所有博客信息')

    const sql = `SELECT p.*, 
    GROUP_CONCAT(t.TagName ORDER BY t.TagName SEPARATOR ',') AS TagNames,
    GROUP_CONCAT(c.ColumnName ORDER BY c.ColumnName SEPARATOR ',') AS ColumnNames
    FROM post p 
    LEFT JOIN post_tag_relation ptr ON p.PostID = ptr.PT_PostID
    LEFT JOIN tag t ON ptr.PT_TagID = t.TagID
    LEFT JOIN post_column_relation pcr ON p.PostID = pcr.PC_PostID
    LEFT JOIN column_info c ON pcr.PC_ColumnID = c.ColumnID
    WHERE Post_UserID = ?
    GROUP BY p.PostID`

    // 执行sql语句
    db.query(sql, [req.user.UserID], (err, results) => {

        // sql执行失败
        if (err) return res.send({ status: 1, message: err.message });

        // 查询结果为空
        if (results.length === 0) return res.send({ status: 1, message: '查询失败' });

        // 成功查询
        res.send({ status: 0, message: '获取当前登录用户博客成功', data: results });

    })



};

// 根据thumbs点赞表 查询热门的博客
exports.getPopularPost = (req, res) => {

    console.log('请求：根据thumbs点赞表 查询热门的博客')
    // sql语句
    const sql = `
        SELECT p.*, 
        COUNT(DISTINCT th.ThumbID) as LikeCount,
        GROUP_CONCAT(t.TagName ORDER BY t.TagName SEPARATOR ',') TagNames,
        GROUP_CONCAT(c.ColumnName ORDER BY c.ColumnName SEPARATOR ',') ColumnNames
        FROM post p
        LEFT JOIN thumbs th ON p.PostID = th.Th_PostID
        LEFT JOIN post_tag_relation ptr ON p.PostID = ptr.PT_PostID
        LEFT JOIN tag t ON ptr.PT_TagID = t.TagID
        LEFT JOIN post_column_relation pcr ON p.PostID = pcr.PC_PostID
        LEFT JOIN column_info c ON pcr.PC_ColumnID = c.ColumnID
        GROUP BY p.PostID
        ORDER BY LikeCount DESC
        limit 4`

    // 执行sql
    db.query(sql, (err, results) => {

        // sql执行失败    
        if (err) return res.send({ status: 1, message: err.message });

        // 查询结果为空
        if (results.length === 0) return res.send({ status: 1, message: '查询失败' });

        // 查询成功
        res.send({ status: 0, message: '获取热门博客列表成功', data: results });
    })
};

// 根据标签id查找博客
exports.getPostByTag = (req, res) => {
    console.log('请求:根据标签id查找博客!!')
    //sql语句 
    const sql = `
        SELECT p.*,
        GROUP_CONCAT(DISTINCT t.TagName ORDER BY t.TagName SEPARATOR ',') TagNames,
        GROUP_CONCAT(DISTINCT c.ColumnName ORDER BY c.ColumnName SEPARATOR ',') ColumnNames
        FROM post p
        LEFT JOIN post_tag_relation ptr ON p.PostID = ptr.PT_PostID
        LEFT JOIN tag t ON ptr.PT_TagID = t.TagID
        LEFT JOIN post_column_relation pcr ON p.PostID = pcr.PC_PostID
        LEFT JOIN column_info c ON pcr.PC_ColumnID = c.ColumnID
        WHERE ptr.PT_TagID = ?
        GROUP BY p.PostID`

    db.query(sql, [req.params.id], (err, results) => {
        // sql执行失败
        if (err) return res.send({ status: 1, message: err.message });
        // 查询结果为空
        if (results.length === 0) return res.send({ status: 1, message: '查询失败' });
        // 查询成功
        res.send({ status: 0, message: '获取博客列表成功', data: results });
    })
};

// 根据分类id查找博客
exports.getPostByColumn = (req, res) => {

    console.log('请求:根据分类查找博客!!')
    const sql = `
        SELECT p.*,
        GROUP_CONCAT(DISTINCT t.TagName ORDER BY t.TagName SEPARATOR ',') TagNames,
        GROUP_CONCAT(DISTINCT c.ColumnName ORDER BY c.ColumnName SEPARATOR ',') ColumnNames
        FROM post p
        LEFT JOIN post_column_relation pcr ON p.PostID = pcr.PC_PostID
        LEFT JOIN column_info c ON pcr.PC_ColumnID = c.ColumnID
        LEFT JOIN post_tag_relation ptr ON p.PostID = ptr.PT_PostID
        LEFT JOIN tag t ON ptr.PT_TagID = t.TagID
        WHERE pcr.PC_ColumnID = ?
        GROUP BY p.PostID`

    db.query(sql, [req.params.id], (err, results) => {
        //sql执行失败
        if (err) return res.send({ status: 1, message: err.message });
        // 查询结果为空
        if (results.length === 0) return res.send({ status: 1, message: '查询失败' });
        // 查询成功
        res.send({ status: 0, message: '获取博客列表成功', data: results });
    })
};

// 根据博客ID查看博客详情
exports.getPostDetail = (req, res) => {

    console.log('请求:根据博客ID查看博客详情')
    const sql = `
        SELECT 
        p.PostID,p.PostTitle,p.PostContent,
        DATE_FORMAT(p.Post_Date, '%Y-%m-%d %H:%i:%s') Post_Date,
        DATE_FORMAT(p.Post_Update_Date, '%Y-%m-%d %H:%i:%s') Post_Update_Date,
        p.Post_Views,u.UserID,u.UserName,
        GROUP_CONCAT(DISTINCT t.TagName ORDER BY t.TagName SEPARATOR ',') TagNames,
        GROUP_CONCAT(DISTINCT c.ColumnName ORDER BY c.ColumnName SEPARATOR ',') ColumnNames
        FROM post p
        INNER JOIN user u ON p.Post_UserID = u.UserID
        LEFT JOIN post_tag_relation ptr ON p.PostID = ptr.PT_PostID
        LEFT JOIN tag t ON ptr.PT_TagID = t.TagID
        LEFT JOIN post_column_relation pcr ON p.PostID = pcr.PC_PostID
        LEFT JOIN column_info c ON pcr.PC_ColumnID = c.ColumnID
        WHERE p.PostID = ?
        GROUP BY p.PostID`; 

    db.query(sql, [req.params.id], (err, results) => {
        // sql执行失败
        if (err) return res.send({ status: 1, message: err.message });
        // 查询结果为空
        if (results.length === 0) return res.send({ status: 1, message: '查询失败' });
        // 查询成功
        res.send({ status: 0, message: '获取博客列表成功', data: results });
    })
}

// 输入关键词搜索博客
exports.selectBlog = (req, res) => {

    console.log('请求:输入关键词搜索博客!!')
    console.log('关键词是:')
    const sql = `
        SELECT p.*,
        GROUP_CONCAT(DISTINCT t.TagName ORDER BY t.TagName SEPARATOR ',') TagNames,
        GROUP_CONCAT(DISTINCT c.ColumnName ORDER BY c.ColumnName SEPARATOR ',') ColumnNames
        FROM post p
        LEFT JOIN post_column_relation pcr ON p.PostID = pcr.PC_PostID
        LEFT JOIN column_info c ON pcr.PC_ColumnID = c.ColumnID
        LEFT JOIN post_tag_relation ptr ON p.PostID = ptr.PT_PostID
        LEFT JOIN tag t ON ptr.PT_TagID = t.TagID
        WHERE p.PostTitle LIKE ? or p.PostContent LIKE ?
        GROUP BY p.PostID`

    db.query(sql, [req.body.keyword, req.body.keyword], (err, results) => {
        // sql执行失败
        if (err) return res.send({ status: 1, message: err.message });
        // 查询结果为空
        if (results.length === 0) return res.send({ status: 1, message: '未查询到相关博客' });
        // 查询成功
        res.send({ status: 0, message: '获取博客列表成功', data: results });
    })
};

//  点击博客详情页时对应浏览数加1
exports.addPostViews = (req, res) => {
    console.log('请求:点击博客详情，更新浏览量!!')
    // 先获取原来的浏览量
    const selectSql = `select Post_Views from post where PostID=?`
    db.query(selectSql, [req.params.id], (err, data) => {
        // sql执行失败
        if (err) return res.send({ status: 1, message: err.message });
        // 查询结果为空
        if (data.length === 0) return res.send({ status: 1, message: '查询失败' });
        // 将浏览数更新
        const updateSql = `update post set Post_Views=? where PostID=?`;
        db.query(updateSql, [data[0].Post_Views+1,req.params.id], (err, results) => {

            // sql执行失败
            if (err) return res.send({ status: 1, message: err.message });

            // 查询结果为空
            if (results.affectedRows !== 1) return res.send({ status: 1, message: '更新浏览数失败' });

            // 查询成功
            res.send({ status: 0, message: '浏览数更新成功'});

        })
    })
}

//删除文章(管理员用)
exports.delpost = (req,res) => {

    console.log('请求:管理员删除博客!!')

    const delSql = `delete from post where PostID = ?`

    db.query(delSql,[req.params.id],(err,results)=>{

        // sql执行失败
        if (err) return res.send({ status: 1, message: err.message });

        // 删除失败
        if (results.affectedRows !== 1) return res.send({ status: 1, message: '删除博客失败' });

        // 删除成功
        res.send({ status: 0, message: '删除该博客成功'}); 

    })

}